package com.glls.sbdemo18.mapper;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.glls.sbdemo18.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @date 2023/5/15
 * @desc Wrapper  条件构造器
 */
@SpringBootTest
public class UserMapperTest2 {
    @Autowired
    private UserMapper userMapper;


    @Test
    public void testDelete(){

        //1、ge    大于等于、gt   大于 、le   小于等于、lt   小于、isNull  为null、isNotNull   不为空

        //删除 年龄大于或等于33的用户信息

        //创建条件构造器对象
        //QueryWrapper<User> wrapper = new QueryWrapper<>();
        //
        //wrapper.ge("age",33);
        //
        //int delete = userMapper.delete(wrapper);

        //UPDATE user SET deleted=1 WHERE deleted=0 AND name IS NULL AND age >= ? AND email IS NOT NULL



    }


    @Test
    public void testSelectOne() {

        //eq    等于 、ne     不等于
        QueryWrapper<User> wrapper = new QueryWrapper<User>();

        wrapper.eq("name","Jack");

        userMapper.selectList(wrapper);

    }


    @Test
    public void testBetween(){
        //3、between、notBetween 包含大小边界
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.between("age",20,24);
        userMapper.selectList(wrapper);
    }


    @Test
    public void testAllEq(){
        //4、allEq
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        Map<String, Object> map = new HashMap<>();
        map.put("id", 2);
        map.put("name", "Jack");
        map.put("age", 20);

        wrapper.allEq(map);
        List<User> users = userMapper.selectList(wrapper);

    }


    @Test
    public void testLike(){

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        //wrapper.like("name","s");
        //wrapper.notLike("name","s");
        //wrapper.likeLeft("name","s");
        wrapper.likeRight("name","S");

        List<User> users = userMapper.selectList(wrapper);

    }

    @Test
    public void testIn(){

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        //wrapper.in("id",1L,2L,3L);
        wrapper.notIn("id",1L,2L,3L);

        List<User> users = userMapper.selectList(wrapper);

    }


    @Test
    public void testInSql(){

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        //wrapper.inSql("id","select id from user where age < 25");
        wrapper.notInSql("id","select id from user where age < 25");

        List<User> users = userMapper.selectList(wrapper);

    }

    @Test
    public void testExists(){

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        //相关子查询
        // exists 后的子查询  返回的有结果 前面的查询才能正常执行
        wrapper.exists("select * from user where id <0");
        //wrapper.notExists("select * from user where id <0");

        List<User> users = userMapper.selectList(wrapper);
        System.out.println(users);
    }


    @Test
    public void testUpdate(){

        // or 和  and 的用法
        //UPDATE user SET name=?, age=?, update_time=?
        // WHERE deleted=0 AND name LIKE ? OR age BETWEEN ? AND ?

        User user = new User();
        user.setName("王五");
        //user.setAge(22);
        //QueryWrapper<User> wrapper = new QueryWrapper<>();
        //wrapper.eq("id",6);
        UpdateWrapper<User> wrapper = new UpdateWrapper<>();

        //wrapper.eq("id",6);
        //wrapper.like("name","三");
        ////  默认 使用  and 连接 多个条件
        //wrapper.between("age",30,35);



        // 两个条件中 任意满足一个 使用  or
        wrapper.eq("id",6)
                .or().eq("name","ls");


        int update = userMapper.update(user, wrapper);

    }



    @Test
    public void testUpdate2(){
        //嵌套的 or 与 and

        // name like  %o%  and age >20  or email is not null

        //UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
        //QueryWrapper<User> wrapper = new QueryWrapper<>();
        //
        //wrapper.like("name","o").gt("age",20)
        //        .or().isNotNull("email");



        // name like  %o%  and (age >20 or email is not null)
        //QueryWrapper<User> wrapper = new QueryWrapper<>();
        //
        //wrapper.like("name","o")
        //        .and((param)->param.gt("age",30).or().isNotNull("email"));



        // name like  %o%  or (age >20 and email is not null)
        QueryWrapper<User> wrapper = new QueryWrapper<>();

        wrapper.like("name","o")
                .or((param)->param.gt("age",20).isNotNull("email"));


        List<User> users = userMapper.selectList(wrapper);


    }

    @Test
    public void testOrder(){

        QueryWrapper<User> wrapper = new QueryWrapper<>();


        // 按照 年龄 升序
        wrapper.orderByAsc("age");

        //按照id   降序
        wrapper.orderByDesc("id");

        wrapper.isNotNull("age");
        List<User> users = userMapper.selectList(wrapper);

        System.out.println(users);
    }



    @Test
    public void testSelectListLast() {
        // 直接  在后面   拼接 sql 语句
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        //queryWrapper.last("limit 1");
        queryWrapper.last("and age > 20");
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }


    @Test
    public void testSelectListColumn() {
        //指定 要查询的列
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("id", "name", "age");

        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);

    }


    @Test
    public void testUpdateSet() {
//修改值
        User user = new User();
        user.setAge(99);
//修改条件
        UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
        userUpdateWrapper
                .like("name", "z")
                .set("name", "老李头")//除了可以查询还可以使用set设置修改的字段
                .setSql(" email = '123@qq.com'");//可以有子查询
        int result = userMapper.update(user, userUpdateWrapper);

    }





    //补充  常常看到 一些方法的第一个参数 是一个 boolean 类型的值  怎么用的
    //  LambdaQueryWrapper   LambdaUpdateWrapper 怎么用的
    @Test
    public void testLambdaWrapper(){
        //QueryWrapper<User> wrapper = new QueryWrapper<>();
        //
        ////wrapper.like("name","五").isNotNull("EMAIL");
        //User user = new User();
        ////user.setName("五");
        //wrapper.like(user.getName()!=null,"name",user.getName()).isNotNull("EMAIL");

        LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();

        wrapper.eq(User::getName,"王五");
        //wrapper.isNotNull(User::getEmail);
        wrapper.isNotNull(User::getEmail);

        userMapper.selectList(wrapper);

    }




}
